<?php

require_once ROOT_PATH . '/lib/confs/Conf.php';
require_once ROOT_PATH . '/lib/dao/DMLFunctions.php';
require_once ROOT_PATH . '/lib/dao/SQLQBuilder.php';
require_once ROOT_PATH . '/lib/common/CommonFunctions.php';
require_once ROOT_PATH . '/lib/common/UniqueIDGenerator.php';
require_once ROOT_PATH . '/lib/common/SearchObject.php';
require_once ROOT_PATH . '/lib/models/hrfunct/EmpInfo.php';
require_once ROOT_PATH . '/lib/models/skillmatrix/SkillExpertise.php';



class SkillGroup {

	const TABLE_NAME = 'hs_hr_skill_group';
	
	const TABLE_QUESTION = 'hs_hr_sur_questions';
	const TABLE_OPTION = 'hs_hr_sur_options';
	const TABLE_ANSWER = 'hs_hr_sur_answers';
	const DB_FIELD_QUESTION_ID = 'question_id';
	const DB_FIELD_OPTION_ID = 'option_id';
	const DB_FIELD_ANSWER_ID = 'answer_id';
	
	const DB_FIELD_SKILL_GROUP_ID = 'ID';
	const DB_FIELD_SKILL_GROUP_NAME = 'GroupName';
	const DB_FIELD_SKILL_GROUP_TYPE = 'GroupType';
	const DB_FIELD_SKILL_GROUP_ORDER = 'ordering';				
	
	private $skill_group_id;
	private $skill_group_name;
	private $skill_group_type;
	private $skill_group_order;
	
	
	public function __construct($id = null) {
		$this->skill_group_id = $id;
	}

	public function setId($id) {
		$this->skill_group_id = $id;
	}
	
	public function getId() {
		return $this->skill_group_id;
	}
	
	public function setSkillGroupName($name) {
		$this->skill_group_name = $name;
	}
	
	public function getSkillGroupName() {
		return $this->skill_group_name;
	}
	
	public function setSkillGroupType($type){
		$this->skill_group_type = $type;
	}
	public function getSkillGrouptype(){
		return $this->skill_group_type;
	}
	
	public function setSkillGroupOrder($oder){
		$this->skill_group_order= $oder;
	}
	public function getSkillGroupOrder(){
		return $this->skill_group_order;
	}

	

    public function save() {
		if (isset($this->skill_group_id)) {
			return $this->_update();
		} else {
			return $this->_insert();
		}
    }

	public static function delete($id) {

		$count = 0;
		$sql = sprintf("DELETE FROM %s WHERE %s IN (%s)", self::TABLE_NAME,
			                self::DB_FIELD_SKILL_GROUP_ID, $id);

			$conn = new DMLFunctions();
			
			$result = $conn->executeQuery($sql);
			if ($result) {
				$count = mysql_affected_rows();
			}
		
		
		return $count;
	}
	

	
	public static function isNameExisted($surveyName,$surveyId=""){
		$selectCondition[0] = "(". self::DB_FIELD_SURVEY_NAME ." = '". trim($surveyName)."' )";	
		$selectCondition[1] = "(". self::DB_FIELD_SURVEY_ID ." != '".$surveyId."' )";	
		$surveys = self::_getList($selectCondition);
		if(count($surveys)>0){
			return true;
		}		
		return false;
	}

	public static function getAll() {
		return self::_getList();
	}
	
	public static function getSkillExpertise($id) {		
		return SkillExpertise::getSkillExpertiseByGroup($id);
	}
	
	public static function getSkillGroup($skillGroupID) {
		$selectCondition[0] = "(". self::DB_FIELD_SKILL_GROUP_ID ." = ". $skillGroupID." )";		
		$skillGroupLists = self::_getList($selectCondition);
		if(count($skillGroupLists)>0){
			return $skillGroupLists[0];
		}else{
			$skillGroup = new SkillGroup();
			return $skillGroup;
		}
	}
	
	

	public static function getSkillGroupByType($grouptype) {
		//$selectCondition[0] =  $grouptype." in (". self::DB_FIELD_SKILL_GROUP_TYPE .")";
		$actList = array();		
		$skillGroupLists = self::_getList();
		if(count($skillGroupLists)>0){
			foreach($skillGroupLists as $skillGroupList){
				if(in_array($grouptype,explode(",",$skillGroupList->getSkillGroupType())))
					$actList[]=$skillGroupList;
			}
		}
		return $actList;
	}

	public static function getMaxId() {
		$sql = sprintf("SELECT MAX(ID) AS NEW_ID FROM %s ", self::TABLE_NAME);
		
			$conn = new DMLFunctions();
			$result = $conn->executeQuery($sql);
			$max_venue = mysql_result($result,0,0);  
		
		return $max_venue;
	}

	private static function _getList($selectCondition = null,$selectOrderBy='ordering',$selectOrder=null, $selectLimit=null) {
		$fields[0] = self::DB_FIELD_SKILL_GROUP_ID;
		$fields[1] = self::DB_FIELD_SKILL_GROUP_NAME;
		$fields[2] = self::DB_FIELD_SKILL_GROUP_TYPE;
		$fields[3] = self::DB_FIELD_SKILL_GROUP_ORDER;
		
		$tables[0] = self::TABLE_NAME ;
		
		$sqlBuilder = new SQLQBuilder();
		$sql = $sqlBuilder->simpleSelect($tables[0],$fields,$selectCondition,$selectOrderBy,$selectOrder, $selectLimit);
		//die($sql);
		$actList = array();

		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);

		while ($result && ($row = mysql_fetch_assoc($result))) {
			$actList[] = self::_createFromRow($row);
		}
		return $actList;
	}

	private function _insert() {
				
		$fields[0] = self::DB_FIELD_SKILL_GROUP_NAME;
		$fields[1] = self::DB_FIELD_SKILL_GROUP_TYPE;
		$fields[2] = self::DB_FIELD_SKILL_GROUP_ORDER;
		
		$values[0] = "'{$this->skill_group_name}'";
		$values[1] = $this->skill_group_type;
		$values[2] = $this->skill_group_order?$this->skill_group_order:0;
	
		$sqlBuilder = new SQLQBuilder();
		$sqlBuilder->table_name = self::TABLE_NAME;
		$sqlBuilder->flg_insert = 'true';
		$sqlBuilder->arr_insert = $values;
		$sqlBuilder->arr_insertfield = $fields;

		$sql = $sqlBuilder->addNewRecordFeature2();
		
		$conn = new DMLFunctions();

		$result = $conn->executeQuery($sql);
		if (!$result || (mysql_affected_rows() != 1)) {
			throw new OptionsException("Insert failed. ", OptionsException::DB_ERROR);
		}
		
		return $this->skill_group_id;
	}

	private function _update() {

		$fields[0] = self::DB_FIELD_SKILL_GROUP_ID;
		$fields[1] = self::DB_FIELD_SKILL_GROUP_NAME;
		$fields[2] = self::DB_FIELD_SKILL_GROUP_TYPE;
		$fields[3] = self::DB_FIELD_SKILL_GROUP_ORDER;

		$values[0] = $this->skill_group_id;
		$values[1] = "'{$this->skill_group_name}'";
		$values[2] = $this->skill_group_type;
		$values[3] = $this->skill_group_order?$this->skill_group_order:0;
		
		
		$sqlBuilder = new SQLQBuilder();
		$sqlBuilder->table_name = self::TABLE_NAME;
		$sqlBuilder->flg_update = 'true';
		$sqlBuilder->arr_update = $fields;
		$sqlBuilder->arr_updateRecList = $values;

		$sql = $sqlBuilder->addUpdateRecord1(0);

		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);

		if (!$result) {
			throw new OptionsException("Update failed. SQL=$sql", OptionsException::DB_ERROR);
		}
		return $this->skill_group_id;
	}

    private static function _createFromRow($row) {
    	
    	$skillGroup = new SkillGroup($row[self::DB_FIELD_SKILL_GROUP_ID]);
    	$skillGroup->setId($row[self::DB_FIELD_SKILL_GROUP_ID]);
    	$skillGroup->setSkillGroupName($row[self::DB_FIELD_SKILL_GROUP_NAME]);
    	$skillGroup->setSkillGroupType($row[self::DB_FIELD_SKILL_GROUP_TYPE]);
    	$skillGroup->setSkillGroupOrder($row[self::DB_FIELD_SKILL_GROUP_ORDER]);
    	
    	return $skillGroup;
    }
    
    
	function getSkillMatrixByGroup($group = null) {
		
        $tables[0] = 'hs_hr_skill_expertise e';        
        $tables[1] = '`hs_hr_skill_group` b';

        $joinConditions[1] = "e.`skillgroupid` = b.`id`";          

        $arrFieldList[0] = 'e.id';
        
        $arrFieldList[1] = 'e.expertisename';
        $arrFieldList[2] = 'b.id';
        $arrFieldList[3] = 'b.groupname';
        $arrFieldList[4] = 'b.grouptype';
        
        if($group){
         	$selectConditions[0] = " b.id = $group";
        }else{
        	$selectConditions = null;
        }
        

        $sql_builder = new SQLQBuilder();
       
        $sqlBuilder = new SQLQBuilder();
        $sqlQString = $sqlBuilder->selectFromMultipleTable($arrFieldList,$tables, $joinConditions,$selectConditions,null,"b.id");
		//die($sqlQString);
        $dbConnection = new DMLFunctions();
        $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function

        $i = 0;
		$arrayDispList = array();
        while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {        	
            $arrayDispList[$i][0] = $line[0];
            $arrayDispList[$i][1] = $line[1];
            $arrayDispList[$i][2] = $line[2];
            $arrayDispList[$i][3] = $line[3];  
            $arrayDispList[$i][4] = $line[4];             
            $i++;        
        }

        if (isset ($arrayDispList)) {

            return $arrayDispList;

        } else {

            return $arrayDispList;

        }
    }
    
   function getSkillMatrixByGroupType($groupType = null) {

        $tables[0] = 'hs_hr_skill_expertise e';        
        $tables[1] = '`hs_hr_skill_group` b';

        $joinConditions[1] = "e.`skillgroupid` = b.`id`";          

        $arrFieldList[0] = 'e.id';
        
        $arrFieldList[1] = 'e.expertisename';
        $arrFieldList[2] = 'b.id';
        $arrFieldList[3] = 'b.groupname';
        $arrFieldList[4] = 'b.grouptype';      

        $sql_builder = new SQLQBuilder();
       
        $sqlBuilder = new SQLQBuilder();
        $sqlQString = $sqlBuilder->selectFromMultipleTable($arrFieldList,$tables, $joinConditions,null,null,"b.id");
		
        $dbConnection = new DMLFunctions();
        $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function

        $i = 0;
		$arrayDispList = array();
        while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
        	if(in_array($groupType,explode(",",$line[4]))){
	            $arrayDispList[$i][0] = $line[0];
	            $arrayDispList[$i][1] = $line[1];
	            $arrayDispList[$i][2] = $line[2];
	            $arrayDispList[$i][3] = $line[3];  
	            $arrayDispList[$i][4] = $line[4];             
	            $i++;
        	}

        }

        if (isset ($arrayDispList)) {

            return $arrayDispList;

        } else {

            return $arrayDispList;

        }
    }
    
	function getSkillMatrixForUser($groupType = null) {

        $tables[0] = 'hs_hr_skill_expertise e';        
        $tables[1] = '`hs_hr_skill_group` b';

        $joinConditions[1] = "e.`skillgroupid` = b.`id`";          

        $arrFieldList[0] = 'e.id';
        
        $arrFieldList[1] = 'e.expertisename';
        $arrFieldList[2] = 'b.id';
        $arrFieldList[3] = 'b.groupname';
        $arrFieldList[4] = 'b.grouptype';      

        $sql_builder = new SQLQBuilder();
       
        $sqlBuilder = new SQLQBuilder();
        $sqlQString = $sqlBuilder->selectFromMultipleTable($arrFieldList,$tables, $joinConditions,null,null,"b.id");

        $dbConnection = new DMLFunctions();
        $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function

        $i = 0;
		$arrayDispList = array();
        while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {
        	if(in_array($groupType,explode(",",$line[4]))){
	            $arrayDispList[$i][0] = $line[0];
	            $arrayDispList[$i][1] = $line[1];
	            $arrayDispList[$i][2] = $line[2];
	            $arrayDispList[$i][3] = $line[3];  
	            $arrayDispList[$i][4] = $line[4];             
	            $i++;
        	}

        }

        if (isset ($arrayDispList)) {

            return $arrayDispList;

        } else {

            return $arrayDispList;

        }
    }
   

}

class SkillGroupException extends Exception {
	const INVALID_PARAMETER = 0;
	const DB_ERROR = 1;
}
?>
